package com.mzsoft.ai.util;

import com.alibaba.fastjson.JSON;
import com.mzsoft.ai.utils.DateUtils;
import com.mzsoft.ai.utils.PrimaryKeyUtil;
import com.mzsoft.ai.utils.file.FileUtils;
import com.mzsoft.ai.utils.poi.ExcelUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * Excel操作工具类
 * @author ChaiXY
 */
public class ExcelUtil {
	
	private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    // @Value("${file_base_path}")
    //private static String fileBasePath;//文件的基础路径
    public static String fileBasePath = System.getProperty("user.dir") + File.separator + "excel" + File.separator;//文件的基础路径

    public static final String OFFICE_EXCEL_XLS = "xls";
    public static final String OFFICE_EXCEL_XLSX = "xlsx";
    
    static {
    	if(!new File(fileBasePath).exists()) {
    		new File(fileBasePath).mkdir();
    	}
    }

    /**
     * 读取指定Sheet也的内容
     * @param filepath filepath 文件全路径
     * @param sheetNo sheet序号,从0开始,如果读取全文sheetNo设置null
     */
    public static String readExcel(String filepath, Integer sheetNo)
            throws InvalidFormatException, IOException {
        StringBuilder sb = new StringBuilder();
        Workbook workbook = getWorkbook(filepath);
        if (workbook != null) {
            if (sheetNo == null) {
                int numberOfSheets = workbook.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    if (sheet == null) {
                        continue;
                    }
                    sb.append(readExcelSheet(sheet));
                }
            } else {
                Sheet sheet = workbook.getSheetAt(sheetNo);
                if (sheet != null) {
                    sb.append(readExcelSheet(sheet));
                }
            }
        }
        return sb.toString();
    }

    /**
     * 根据路径加载解析Excel
     * @param file
     * @return
     */
    public static List<Map<String, String>> parseExcel(File file) {
        List<Map<String, String>> list = new ArrayList<>();
        InputStream input = null;
        Workbook workBook = null;        
        Sheet sheet = null;
      //  File file = null;
        String path = file.getName();
        if (path != null && path.length() > 7) {
            //判断文件是否是Excel(2003、2007)
            String suffix = path.substring(path.lastIndexOf("."), path.length());
            if (".xls".equals(suffix) || ".xlsx".equals(suffix)) {// 2003后缀或2007后缀
            //    file = new File(path);
                try {
                    input = new FileInputStream(file);
                } catch (FileNotFoundException e) {
                	logger.error("未找到指定的文件！");
                    e.printStackTrace();
                } catch (Exception e) {
                	logger.error("读取Excel文件发生异常！");
                    e.printStackTrace();
                }
                if (!input.markSupported()) {
                    input = new PushbackInputStream(input, 8);
                }
                try {
                    workBook = WorkbookFactory.create(input);
                } catch (IOException e) {
                	logger.error("创建表格工作簿对象发生IO异常！原因：" + e.getMessage());
                    e.printStackTrace();
                }

                try {
                    if (workBook != null) {
                        int numberSheet = workBook.getNumberOfSheets();
                        if (numberSheet > 0) {
                            sheet = workBook.getSheetAt(0);//获取第一个工作簿(Sheet)的内容【注意根据实际需要进行修改】
                            list = getExcelContent(sheet);
                        } else {
                        	logger.error("目标表格工作簿(Sheet)数目为0！");
                        }
                    }
                    input.close();
                } catch (IOException e) {
                	logger.error("关闭输入流异常！" + e.getMessage());
                    e.printStackTrace();
                }
            } else {
            	logger.error("非法的Excel文件后缀！");
            }
        } else {
        	logger.error("非法的文件路径!");
        }
        return list;
    }
    
    /**
     * 解析(读取)Excel内容
     *
     * @param sheet
     * @return
     */
    public static List<Map<String, String>> getExcelContent(Sheet sheet) {
        List<Map<String, String>> list = new ArrayList<>();
        int rowCount = sheet.getPhysicalNumberOfRows();//总行数
        if (rowCount > 1) {
            Row titleRow = sheet.getRow(0);//标题行
            for (int i = 1; i < rowCount; i++) {//遍历行，略过标题行，从第二行开始
                Row row = sheet.getRow(i);
                Map<String, String> entity = new HashMap<>();
                int columNos = row.getLastCellNum();// 表头总共的列数
                for (int j = 0; j < columNos; j++) {
                    Cell cell = row.getCell(j);
                    String key = titleRow.getCell(j).getStringCellValue();
                    String val = "";
                    if(cell != null) {
                    	cell.setCellType(CellType.STRING);
                        val = cell.getStringCellValue();
                    }                    
                    entity.put(key,val);
                }
                list.add(entity);
            }
        }
        return list;
    }

    /**
     * 读取指定Sheet也的内容
     * @param filepath filepath 文件全路径
     * @param sheetNo sheet序号,从0开始,如果读取全文sheetNo设置null
     */
    public static List<Map<String, Object>> readExcelMap(String filepath, Integer sheetNo,Integer startRow)
            throws InvalidFormatException, IOException {

        logger.debug("filePath:"+filepath);
        List<Map<String, Object>> list = new ArrayList<>();
        Workbook workbook = getWorkbook(filepath);
        if (workbook != null) {
            if (sheetNo == null) {
                int numberOfSheets = workbook.getNumberOfSheets();
                for (int i = 0; i < numberOfSheets; i++) {
                    Sheet sheet = workbook.getSheetAt(i);
                    if (sheet == null) {
                        continue;
                    }
                    list = readExcelSheetMap(sheet);
                }
            } else {
                Sheet sheet = workbook.getSheetAt(sheetNo);
                if (sheet != null) {
                    list = readExcelSheetMap2(sheet,startRow);
                }
            }
        }
        return list;
    }

    /**
     * 读取excel中多个sheet工作区
     * @param filePath 文件路径
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static Map<String, Object> readExcelMultiSheetMap(String filePath,Integer startRow) throws IOException, InvalidFormatException {

        Map<String,Object> result = new HashMap<>();

        logger.debug("filePath:"+filePath);

        Workbook workbook = getWorkbook(filePath);
        if (workbook != null) {
            int numberOfSheets = workbook.getNumberOfSheets();
            String recordFileName = "导入统计";
            for (int i = 0; i < numberOfSheets; i++) {

                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) {
                    continue;
                }

                FileUtils.fileLinesWrite(fileBasePath + recordFileName,filePath+"========导入工作区："+i,true);
                FileUtils.fileLinesWrite(fileBasePath+ recordFileName,"导入行数："+(sheet.getLastRowNum()-1),true);

                List<Map<String, Object>> list = readExcelSheetMap2(sheet,startRow);
                result.put(i+"",list);
            }
        }
        return  result;
    }

    /**
     * 根据文件路径获取Workbook对象
     * @param filepath 文件全路径
     */
    public static Workbook getWorkbook(String filepath)
            throws InvalidFormatException, IOException {
        InputStream is = null;
        Workbook wb = null;
        if (StringUtils.isBlank(filepath)) {
            throw new IllegalArgumentException("文件路径不能为空");
        } else {
            String suffiex = getSuffiex(filepath);
            if (StringUtils.isBlank(suffiex)) {
                throw new IllegalArgumentException("文件后缀不能为空");
            }
            if (OFFICE_EXCEL_XLS.equals(suffiex) || OFFICE_EXCEL_XLSX.equals(suffiex)) {
                try {
                    is = new FileInputStream(filepath);
                    wb = WorkbookFactory.create(is);
                } finally {
                    if (is != null) {
                        is.close();
                    }
                    if (wb != null) {
                        wb.close();
                    }
                }
            } else {
                throw new IllegalArgumentException("该文件非Excel文件");
            }
        }
        return wb;
    }

    /**
     * 获取后缀
     * @param filepath filepath 文件全路径
     */
    private static String getSuffiex(String filepath) {
        if (StringUtils.isBlank(filepath)) {
            return "";
        }
        int index = filepath.lastIndexOf(".");
        if (index == -1) {
            return "";
        }
        return filepath.substring(index + 1, filepath.length());
    }

    /**
     * 读取excel后生成的list中map的key 以标题为主
     * @param sheet 工作区
     * @param startRow 第几行读取
     * @return
     */
    private static List<Map<String, Object>> readExcelSheetMap2(Sheet sheet,int startRow) throws IOException {
        List<Map<String, Object>> list = new ArrayList<>();

        if(sheet != null){
            int rowNos = sheet.getLastRowNum();// 得到excel的总记录条数

            Row titleRow = sheet.getRow(0);

            for (int i = startRow; i <= rowNos; i++) {// 遍历行
                Row row = sheet.getRow(i);
                if(row != null){
                    Map<String,Object> map = new HashMap<>();
                    int columNos = titleRow.getLastCellNum();//row.getLastCellNum();// 表头总共的列数
                    for (int j = 0; j < columNos; j++) {
                        Cell cell = row.getCell(j);
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                            map.put(String.valueOf(titleRow.getCell(j)),cell.getStringCellValue());
                         //   System.out.print(cell.getStringCellValue() + " ");
                        }else{
                            map.put(String.valueOf(titleRow.getCell(j)),"");
                        }
                    }
                    list.add(map);
                }
            }
        }

        return list;
    }

    private static List<Map<String, Object>> readExcelSheetMap(Sheet sheet) {
        List<Map<String, Object>> list = new ArrayList<>();

        if(sheet != null){
            int rowNos = sheet.getLastRowNum();// 得到excel的总记录条数
            for (int i = 0; i <= rowNos; i++) {// 遍历行
                Row row = sheet.getRow(i);
                if(row != null){
                    Map<String,Object> map = new HashMap<>();
                    int columNos = row.getLastCellNum();// 表头总共的列数
                    for (int j = 0; j < columNos; j++) {
                        Cell cell = row.getCell(j);
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                            map.put(String.valueOf(j),cell.getStringCellValue());
                            System.out.print(cell.getStringCellValue() + " ");
                        }
                    }
                    list.add(map);
                }
            }
        }

        return list;
    }

    private static String readExcelSheet(Sheet sheet) {
        StringBuilder sb = new StringBuilder();

        if(sheet != null){
            int rowNos = sheet.getLastRowNum();// 得到excel的总记录条数
            for (int i = 0; i <= rowNos; i++) {// 遍历行
                Row row = sheet.getRow(i);
                if(row != null){
                    int columNos = row.getLastCellNum();// 表头总共的列数
                    for (int j = 0; j < columNos; j++) {
                        Cell cell = row.getCell(j);
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                            sb.append(cell.getStringCellValue() + " ");
                            System.out.print(cell.getStringCellValue() + " ");
                        }
                    }
                    System.out.println();
                }
            }
        }

        return sb.toString();
    }

    /**
     * 读取指定Sheet页的表头
     * @param filepath filepath 文件全路径
     * @param sheetNo sheet序号,从0开始,必填
     */
    public static Row readTitle(String filepath, int sheetNo)
            throws IOException, EncryptedDocumentException, InvalidFormatException {
        Row returnRow = null;
        Workbook workbook = getWorkbook(filepath);
        if (workbook != null) {
            Sheet sheet = workbook.getSheetAt(sheetNo);
            returnRow = readTitle(sheet);
        }
        return returnRow;
    }

    /**
     * 读取指定Sheet页的表头
     */
    public static Row readTitle(Sheet sheet) throws IOException {
        Row returnRow = null;
        int totalRow = sheet.getLastRowNum();// 得到excel的总记录条数
        for (int i = 0; i < totalRow; i++) {// 遍历行
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            returnRow = sheet.getRow(0);
            break;
        }
        return returnRow;
    }

    /**
     * 创建Excel文件
     * @param filepath filepath 文件全路径
     * @param sheetName 新Sheet页的名字
     * @param titles 表头
     * @param dataList 每行的单元格
     */
    public static boolean writeExcel(String filepath, String sheetName, Map<String,String> titles,
                                     List<Map<String, Object>> dataList) throws IOException {
        boolean success = false;
        OutputStream outputStream = null;
        if (StringUtils.isBlank(filepath)) {
            throw new IllegalArgumentException("文件路径不能为空");
        } else {
            String suffiex = getSuffiex(filepath);
            if (StringUtils.isBlank(suffiex)) {
                throw new IllegalArgumentException("文件后缀不能为空");
            }
            Workbook workbook;
            Map<String, CellStyle> styles;
            if ("xls".equals(suffiex.toLowerCase())) {
                workbook = new HSSFWorkbook();
                styles = createHSSFStyles(workbook);
            } else {
                workbook = new XSSFWorkbook();
                styles =  createStyles(workbook);
            }
            // 生成一个表格
            Sheet sheet;
            if (StringUtils.isBlank(sheetName)) {
                // name 为空则使用默认值
                sheet = workbook.createSheet();
            } else {
                sheet = workbook.createSheet(sheetName);
            }
            // 设置表格默认列宽度为15个字节
//            sheet.setDefaultColumnWidth((short) 15);

            // 创建标题行
            Row row = sheet.createRow(0);
            // 存储标题在Excel文件中的序号
            int id = 0;
            logger.info(JSON.toJSONString(titles));           
            for (String key : titles.keySet()) {
                Cell cell = row.createCell(id);
                cell.setCellStyle(styles.get("header"));
                cell.setCellValue(titles.get(key));
                id++;
            }
            int rowNo = 1;
            // 写入正文
		   for(Map<String, Object> map : dataList) {
			   int i = 0;
			   row = sheet.createRow(rowNo);
			   for(String title : titles.keySet()) {
	                // 在指定序号处创建cell
	                Cell cell = row.createCell(i);

                    cell.setCellStyle(styles.get("cellA"));

	                // 获取列的值
	                Object object = map.get(title);
	                // 判断object的类型
	                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	                if (object instanceof Double) {
	                    cell.setCellValue((Double) object);
	                } else if (object instanceof Date) {
	                    String time = simpleDateFormat.format((Date) object);
	                    cell.setCellValue(time);
	                } else if (object instanceof Calendar) {
	                    Calendar calendar = (Calendar) object;
	                    String time = simpleDateFormat.format(calendar.getTime());
	                    cell.setCellValue(time);
	                } else if (object instanceof Boolean) {
	                    cell.setCellValue((Boolean) object);
	                } else {
	                    if (object != null) {
	                        cell.setCellValue(object.toString());
	                    }
	                }
                   if("num".equalsIgnoreCase(title)){
                       cell.setCellValue(rowNo);
                   }

                   i++;
			   }
			   rowNo++;
		   }

		   //设置列宽自适应
            setColWidth(sheet);

            try {
                outputStream = new FileOutputStream(filepath);
                workbook.write(outputStream);
                success = true;
            } finally {
                if (outputStream != null) {
                    outputStream.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            }
            return success;
        }
    }

    /**
     * 列表宽度自适应
     * @param sheet
     */
    private static void setColWidth(Sheet sheet){

        int maxColumn = sheet.getRow(0).getPhysicalNumberOfCells();
        for(int i = 0; i < maxColumn; i++){
            sheet.autoSizeColumn(i);
        }
        for(int columnNum = 0; columnNum <= maxColumn; columnNum++){
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for(int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++){
                Row currentRow;
                if(sheet.getRow(rowNum) == null){
                    currentRow = sheet.createRow(rowNum);
                }else{
                    currentRow = sheet.getRow(rowNum);
                }

                if(currentRow.getCell(columnNum) != null){
                    Cell currentCell = currentRow.getCell(columnNum);
                    try {
                        int length = currentCell.toString().getBytes("GBK").length;
                        if(columnWidth < length + 1){
                            columnWidth = length + 8;
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }
    
    private static Map<String, CellStyle> createHSSFStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<>();

        // 文件头样式
        HSSFCellStyle headerStyle = (HSSFCellStyle) wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setWrapText(false);
        headerStyle.setBorderRight(BorderStyle.THIN); // 设置边界
        headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setFontName("微软雅黑");
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.BLUE_GREY.index);
        headerStyle.setFont(headerFont);
        styles.put("header", headerStyle);
        
        Font cellStyleFont = wb.createFont();
        cellStyleFont.setFontHeightInPoints((short) 10);
        cellStyleFont.setFontName("微软雅黑");
        
        // 正文样式A
        HSSFCellStyle cellStyleA = (HSSFCellStyle) wb.createCellStyle();
        cellStyleA.setAlignment(HorizontalAlignment.CENTER); // 居中设置
        cellStyleA.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleA.setWrapText(false);
        cellStyleA.setBorderRight(BorderStyle.THIN);
        cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderLeft(BorderStyle.THIN);
        cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderTop(BorderStyle.THIN);
        cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderBottom(BorderStyle.THIN);
        cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setFont(cellStyleFont);
        styles.put("cellA", cellStyleA);

        // 正文样式B:添加前景色为浅黄色
        HSSFCellStyle cellStyleB = (HSSFCellStyle) wb.createCellStyle();
        cellStyleB.setAlignment(HorizontalAlignment.CENTER);
        cellStyleB.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleB.setWrapText(false);
        cellStyleB.setBorderRight(BorderStyle.THIN);
        cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderLeft(BorderStyle.THIN);
        cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderTop(BorderStyle.THIN);
        cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderBottom(BorderStyle.THIN);
        cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setFont(cellStyleFont);
        styles.put("cellB", cellStyleB);
        
        return styles;
    }

    /**
     * 设置格式
     */
    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<>();

        // 文件头样式
        XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setWrapText(false);
        headerStyle.setBorderRight(BorderStyle.THIN); // 设置边界
        headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setFontName("微软雅黑");
        headerStyle.setFont(headerFont);
        styles.put("header", headerStyle);

        Font cellStyleFont = wb.createFont();
        cellStyleFont.setFontHeightInPoints((short) 10);
        cellStyleFont.setFontName("微软雅黑");

        // 正文样式A
        XSSFCellStyle cellStyleA = (XSSFCellStyle) wb.createCellStyle();
        cellStyleA.setAlignment(HorizontalAlignment.CENTER); // 居中设置
        cellStyleA.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleA.setWrapText(false);
        cellStyleA.setBorderRight(BorderStyle.THIN);
        cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderLeft(BorderStyle.THIN);
        cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderTop(BorderStyle.THIN);
        cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderBottom(BorderStyle.THIN);
        cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setFont(cellStyleFont);
        styles.put("cellA", cellStyleA);

        // 正文样式B:添加前景色为浅黄色
        XSSFCellStyle cellStyleB = (XSSFCellStyle) wb.createCellStyle();
        cellStyleB.setAlignment(HorizontalAlignment.CENTER);
        cellStyleB.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleB.setWrapText(true);
        cellStyleB.setBorderRight(BorderStyle.THIN);
        cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderLeft(BorderStyle.THIN);
        cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderTop(BorderStyle.THIN);
        cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderBottom(BorderStyle.THIN);
        cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setFont(cellStyleFont);
        styles.put("cellB", cellStyleB);

        return styles;
    }



    /**
     * 将源文件的内容复制到新Excel文件(可供理解Excel使用,使用价值不大)
     * @param srcFilepath 源文件全路径
     * @param desFilepath 目标文件全路径
     */
    public static void writeExcel(String srcFilepath, String desFilepath)
            throws IOException, EncryptedDocumentException, InvalidFormatException {
        FileOutputStream outputStream = null;
        String suffiex = getSuffiex(desFilepath);
        if (StringUtils.isBlank(suffiex)) {
            throw new IllegalArgumentException("文件后缀不能为空");
        }
        Workbook workbook_des;
        if ("xls".equals(suffiex.toLowerCase())) {
            workbook_des = new HSSFWorkbook();
        } else {
            workbook_des = new XSSFWorkbook();
        }

        Workbook workbook = getWorkbook(srcFilepath);
        if (workbook != null) {
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int k = 0; k < numberOfSheets; k++) {
                Sheet sheet = workbook.getSheetAt(k);
                Sheet sheet_des = workbook_des.createSheet(sheet.getSheetName());
                if (sheet != null) {
                    int rowNos = sheet.getLastRowNum();
                    for (int i = 0; i <= rowNos; i++) {
                        Row row = sheet.getRow(i);
                        Row row_des = sheet_des.createRow(i);
                        if(row != null){
                            int columNos = row.getLastCellNum();
                            for (int j = 0; j < columNos; j++) {
                                Cell cell = row.getCell(j);
                                Cell cell_des = row_des.createCell(j);
                                if(cell != null){
                                    cell_des.setCellType(CellType.STRING);
                                    cell_des.setCellValue(cell.getStringCellValue());
                                }
                            }
                        }
                    }
                }

            }
        }

        try {
            outputStream = new FileOutputStream(desFilepath);
            workbook_des.write(outputStream);
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
            if (workbook != null) {
                workbook_des.close();
            }
        }
    }

    public static void exportExcel(HttpServletResponse response, String filePath, String sheetName, Map<String,String> titles,
                                   List<Map<String, Object>> dataList) {
        try {

            ExcelUtils.writeExcel(filePath,sheetName,titles,dataList);
            File file = new File(filePath);
            InputStream is = new FileInputStream(file);
            InputStream inputStream = new BufferedInputStream(is);

            String fileName = new String(file.getName().getBytes("GBK"), "ISO-8859-1"); // 取消乱码
            response.setContentType("octets/stream");
            response.addHeader("Content-Disposition", "attachment;filename="
                    + fileName);

            BufferedOutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            int len = 0;
            while ((len = inputStream.read()) != -1){
                outputStream.write(len);
                outputStream.flush();
            }
            inputStream.close();
            outputStream.close();
            System.out.println("成功导出Excel，excel名为："+fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {

    }


}
